const connection = require('../app/database')

// 注意坚持sql语句的冒号

const sqlFragment = `
    SELECT m.id id,m.content content,m.createAt createTime,m.updateAt updateTime,JSON_OBJECT('id',u.id,'name',u.name) author
    FROM moment m
    LEFT JOIN user u ON m.user_id=u.id
`

class MomentService {
    // 一，插入动态
    async create(userId, content) {
        try {
            const statement = `INSERT INTO moment (content,user_id) VALUES (?,?);`
            const [result] = await connection.execute(statement, [content, userId]) // 这里的[result]是什么鬼
            return result
        } catch (error) {
            console.log(error);
        }
    }

    // 二，查询动态（单个）
    async getMomentById(id) {
        try {
            const statement = `SELECT m.id id,m.content content,m.createAt createTime,m.updateAt updateTime,
            JSON_OBJECT('id',u.id,'name',u.name,'avatarUrl',u.avatar_url) author,
            IF(l.id,JSON_ARRAYAGG(
                JSON_OBJECT('id',l.id,'name',l.name)
            ) ,NULL) labels,
            (SELECT IF(COUNT(c.id),JSON_ARRAYAGG(
                JSON_OBJECT('id',c.id,'content',c.content,'commentId',c.comment_id,'creatTime',c.createAt,
                         'user',JSON_OBJECT('id',cu.id,'name',cu.name,'avatarUrl',cu.avatar_url))
            ) ,NULL) FROM comment c LEFT JOIN user cu ON c.user_id =cu.id WHERE m.id =c.moment_id) comments,
            (SELECT JSON_ARRAYAGG(CONCAT('http://localhost:8888/moment/images/',file.filename)) FROM file WHERE m.id=file.moment_id) images
            FROM moment m
            LEFT JOIN user u ON m.user_id=u.id
            LEFT JOIN moment_label ml ON m.id=ml.moment_id
            LEFT JOIN label l ON ml.label_id = l.id
            WHERE m.id=?
            GROUP BY m.id;`
            const [result] = await connection.execute(statement, [id])
            return result[0]
        } catch (error) {
            console.log(error);
        }
    }

    // 三，查询动态（多个）
    async getMomentList(offset, size) {
        try {
            const statement = `SELECT m.id id,m.content content,m.createAt createTime,m.updateAt updateTime,JSON_OBJECT('id',u.id,'name',u.name,'avatarUrl',u.avatar_url) author
                ,(SELECT COUNT(*) FROM comment c WHERE c.moment_id=m.id) commentCount,
                (SELECT COUNT(*) FROM moment_label ml WHERE ml.moment_id=m.id) labelCount,
                (SELECT JSON_ARRAYAGG(CONCAT('http://localhost:8888/moment/images/',file.filename)) FROM file WHERE m.id=file.moment_id) images
                FROM moment m
                LEFT JOIN user u ON m.user_id=u.id LIMIT ?,?;`
            const [result] = await connection.execute(statement, [offset, size])
            return result
        } catch (error) {
            console.log(error);
        }
    }

    // 四，修改动态
    async update(content, momentId) {
        try {
            const statement = `UPDATE moment SET content = ? WHERE id = ?;`
            const [result] = await connection.execute(statement, [content, momentId])
            return result
        } catch (error) {
            console.log(error);
        }
    }

    // 四，删除动态
    async remove(momentId) {
        try {
            const statement = `DELETE FROM moment WHERE id = ?;`
            const [result] = await connection.execute(statement, [momentId])
            return result
        } catch (error) {
            console.log(error);
        }
    }

    // 五，给动态添加标签
    async addLabel(momentId, labelId) {
        try {
            const statement = `INSERT INTO moment_label (moment_id,label_id) VALUES (?,?);`
            const [result] = await connection.execute(statement, [momentId, labelId])
            return result
        } catch (error) {
            console.log(error);
        }
    }

    // 查询标签是否存在
    async hasLabel(momentId, labelId) {
        try {
            const statement = `SELECT * FROM moment_label WHERE moment_id=? AND label_id=?;`
            const [result] = await connection.execute(statement, [momentId, labelId])
            return result[0] ? true : false
        } catch (error) {
            console.log(error);
        }
    }
}

module.exports = new MomentService()